SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[usp_FormatCityByProvince]
       (
         @fdPlaceName VARCHAR(50)
       )
AS 
       IF NOT (
                @fdPlaceName = '香港特别行政区'
                OR @fdPlaceName = '台湾省'
                OR @fdPlaceName = '澳门特别行政区'
              ) 
          SELECT    '--请选择--' AS fdPlaceName
          UNION ALL
          SELECT DISTINCT
                    SUBSTRING(fdPlaceName , LEN(@fdPlaceName) + 1 ,
                              LEN(fdPlaceName) - LEN(@fdPlaceName))
          FROM      [FS_Place]
          WHERE     ( [fdPlaceName] LIKE @fdPlaceName + '%' )
                    AND fdPlaceName != ''
                    AND fdPlaceName != '--请选择--'
                    AND RIGHT(fdPlaceName , 1) != '省'
                    AND fdPlaceName!=@fdPlaceName
       ELSE 
          SELECT    '--请选择--' AS fdPlaceName
          UNION ALL
          SELECT DISTINCT
                    [fdPlaceName]
          FROM      [FS_Place]
          WHERE fdPlaceName=@fdPlaceName
GO
